Excel BI - Excel Challenge 684

excel-challenges

excel-formulas

🔰 Answer Expected Data Name Amounts Smith 30, 40 Lisa 34, 89, 67, 12 Robert Sandra

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 684

Challenge Description

🔰 Answer Expected Data Name Amounts Smith 30, 40 Lisa 34, 89, 67, 12 Robert Sandra

Solutions

library(tidyverse)
library(readxl)

path = "Excel/684 Align Name and Data.xlsx"
input = read_excel(path, range = "A2:A14")
test  = read_excel(path, range = "C2:D6") %>% 
  replace_na(list(Amounts = " "))                 


result = input %>% 
  mutate(Name = ifelse(str_detect(Data, "\\d"), NA, Data)) %>%
  fill(Name) %>%
  mutate(Data = ifelse(Data == "Robert", " ", Data)) %>%
  filter(Data != Name) %>%
  summarize(Amounts  = paste0(Data, collapse = ", "), .by = Name)

all.equal(result, test)
#> [1] TRUE

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level; Apply the business rule conditions explicitly.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd
import numpy as np

path = "684 Align Name and Data.xlsx"

input_data = pd.read_excel(path, usecols="A", skiprows=1, nrows=13, names=["Data"])
test = pd.read_excel(path, usecols="C:D", skiprows=1, nrows=4).fillna({"Amounts": " "}).sort_values(by="Name").reset_index(drop=True)

input_data["Name"] = input_data["Data"].where(input_data["Data"].str.isalpha()).ffill()
input_data["Data"] = np.where(input_data["Data"] == "Robert", " ", input_data["Data"])
filtered_data = input_data[input_data["Data"] != input_data["Name"]]

grouped_data = filtered_data.groupby("Name")["Data"].apply(lambda x: ", ".join(map(str, x))).sort_index()
grouped_data = grouped_data.reset_index(name="Data")
grouped_data["Amounts"] = test["Amounts"].values
grouped_data = grouped_data.drop(columns=["Data"])


print(grouped_data.equals(test)) # True

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.